
package ru.athena.runTool.Tests;

import java.io.IOException;
import java.util.List;

import org.antlr.runtime.CharStream;
import org.antlr.runtime.CommonTokenStream;
import org.antlr.runtime.RecognitionException;
import org.testng.annotations.Test;

import ru.athena.runTool.Parser.CaseInsensitiveStringStream;
import ru.athena.runTool.Parser.PLSQLLexer;
import ru.athena.runTool.Parser.PLSQLParser;
import ru.athena.runTool.blockTypes.CodeBlock;
import ru.athena.runTool.blockTypes.Script;
import ru.athena.runTool.blockTypes.StoredObject;

public class PLSQLTests {

	private static final String CURSOR_DECL = 
		"cursor curDoc is select * from XMLSchema " +
		"			where Classified = Message.idObject;\n" +
		"	cursor curX() as select * from dual;\n" +
		"	cursor curX( param number, par varchar2) as select 1,2 from dual;\n" +				
		"	cursor curX as select * from dual;\n" +
		"	cursor curX is select * from dual where a = rec.doc;\n";
	private static String NORMAL_FUNCTION_FORTEST = 
			"	function localF return varchar2 is\n" +
			"		pragma automomous_transaction;\n" +
			"	begin\n" +
			"		raise NO_DATA_FOUND;\n" +
			"		return tNumberArray(1,2,4);\n" +
			"	end localF;\n";
	private static String NORMAL_PROC_FORTEST = 
			"	procedure localProc( param1 varchar2 )\n" + 
			"	is\n" + 
			"	begin\n" + 
			"		someProcedureCall();\n" + 
			"		var := someFunctionCall(param1);\n" +
			"		raise NO_DATA_FOUND;\n" + 
			"	end; \n";
	private static String LOCAL_VARS_DECL_FORTEST =
		"	l number := 1;\n" +
		"	l constant number := 1;\n" +
		"	\"some identifier\" number;\n" +		
		"	e exception;\n" +				
		"	l number := '1';" +				
		"	l number default null;" +
		"	locVar number := initializeValue;" +
		"	locVar number := initializeValue();" +
		"	locVar number := initializeValue(0);" +				
		"	locVar number := initializeValue(param);" +
		"	v 	raw(32);\n" +
		"	v 	blob;\n" +
		"	v 	clob;\n" +
		"	v 	bfile;\n" +		
		"	var 	number := 'string'||'as''df';\n" +
		"	var 	number := true;\n" +
		"	var 	number := true and false;\n" +				
		"	var 	number := 123;\n" +
		"	var 	scheme.tab%rowtype;\n" +
		"	var 	scheme.tab.field%type;\n" +
		"	var 	tab%rowtype;\n" +
		"	var 	tab.field%type;\n" +
		// possible too
		"	var 	scheme.tab % rowtype;\n" +
		"	var 	scheme.tab.field% type;\n" +
		"	var 	tab %rowtype;\n" +
		"	var 	number := 123 + 5 * 2 / 4 - 10;\n";

	@Test
	public void testSqlPlusCommand() throws IOException, RecognitionException {
		Tester t = new Tester(	"set serveroutput on size 20", 
								"SqlPlusCmd", 1 );
		assert t.passed() :	t.getMessage();		
		
		t = new Tester("set serveroutput off" , "SqlPlusCmd", 1 );
		assert t.passed() :	t.getMessage();

		t = new Tester("comment on table  a is 'a';" , "AnonymousBlock", 1,
				"comment on table  a is 'a'");
		assert t.passed() :	t.getMessage();		

		t = new Tester("comment on column  a.b is 'a';" , "AnonymousBlock", 1,
				"comment on column  a.b is 'a'");
		assert t.passed() :	t.getMessage();		
		
		t = new Tester("comment on procedure a.b is 'a';" , "AnonymousBlock", 1,
				"comment on procedure a.b is 'a'");
		assert t.passed() :	t.getMessage();		

		t = new Tester("/" , "SqlPlusCmd", 1 );
		assert t.passed() :	t.getMessage();

		t = new Tester(
				"prompt! some text!:!@#$% ;;'\n" +
				"show error" , "SqlPlusCmd" , 1,
				"show error");
		assert t.passed() :	t.getMessage();
		
		t = new Tester(
				"show error\n" +
				"prompt! some text!:!@#$% ;;'"
				, "SqlPlusCmd" , 1,
				"show error");
		assert t.passed() :	t.getMessage();

		t = new Tester("show error" , "SqlPlusCmd", 1 );
		assert t.passed() :	t.getMessage();
		
		t = new Tester("show errors package body od.bo" , "SqlPlusCmd", 1 );
		assert t.passed() :	t.getMessage();

	}

	@Test
	public void testLiterals() throws IOException, RecognitionException  {
		Tester t = new Tester(
				"declare\n" + 
					LOCAL_VARS_DECL_FORTEST + 
				"begin null; end;",
				"AnonymousBlock", 1);
		assert t.passed() :	t.getMessage();
	
	}

	
	@Test
	public void testAnonymousBlock() throws IOException, RecognitionException  {
		Tester t = new Tester(
				"declare\n" +
					LOCAL_VARS_DECL_FORTEST +
				"begin\n" +
				"\tvar5 := 5;\n" +
				"end;",
				"AnonymousBlock", 1);
		assert t.passed() :	t.getMessage();
		
		t = new Tester(
					"execute CheckScriptDepend('SWFT',0224)" ,
					"AnonymousBlock" , 1,
					"begin CheckScriptDepend('SWFT',0224); end;");
		assert t.passed() :	t.getMessage();

		t = new Tester(
				"begin\n null;\n end;",
				"AnonymousBlock" , 1);
		assert t.passed() :	t.getMessage();		
		
		t = new Tester("exec SomeProc;","AnonymousBlock" , 1, "begin SomeProc; end;");
		assert t.passed() :	t.getMessage();

		// there was strong performance hit (proportionally string expressions count), 
		// grammar fixed (make  stubs like "~(';')+  ';'"  )  
		String block = 
				"begin\n" +
				"	var := " +
				"	 'create or replace trigger T_TransliterateType_Create'||CHR(10)||" +
				"    'before insert or update or delete on TransliterateType'||CHR(10)||" +
				"    'for each row'||CHR(10)||" +
				"    'declare'||CHR(10)||" +
				"    '   var number;'||CHR(10)||" +
				"    'begin'||CHR(10)||" +
				"    '   if INSERTING then'||CHR(10)||" +
				"    '      if :new.Classified is null or :new.Classified=0 then'||CHR(10)||" +
				"    '         null;'||CHR(10)||" +
				"    '      end if;'||CHR(10)||" +
				"	 '   end if;'||CHR(10)||" +
				"    'end;';" +
				"end;";
		t = new Tester( block , "AnonymousBlock" , 1 );
		assert t.passed() :	t.getMessage();	
	}

	
	@Test
	public void testIfBlock() throws IOException, RecognitionException  {
		Tester t = new Tester(
				"begin\n" +
				"	IF true then\n" +
				"		null;\n" +
				"	elsif (someVar and somePar) then\n" +
				"		null;\n" + 
				"	elsif false then\n" +  
				"		null;\n" +
				"	elsif (id is null and null is not null) then\n" +  
				"		null;\n" +
				"	elsif id is null and null is not null then\n" +  
				"		if SQL%ROWCOUNT > 0 then\n" +
				"			null;\n" +
				"		end if;\n" +
				"	else\n" +
				"		if n is null and ((p=0 and s=n) or t=1 ) then\n" +
				"			null;\n" +
				"		elsif (r is null and (p or (k=5 or c=12)))	then\n" +
				"			null;\n" +
				"		elsif n is null and ((p=0 and s=n) or t=1 ) then\n" +
				"			return;\n" +
				"		end if;\n" +
				"	end if;\n" +
				"end;",
				"AnonymousBlock", 1);
		assert t.passed() :	t.getMessage();
		
		t = new Tester(
				"begin\n" +
				"	if sText is null then" +
				"      return;" +
				"   elsif nTransliterateType is not null then" +
				"      begin" +
				"         select Classified into nType from TransliterateType" +
				"          where Classified = nTransliterateType or Const = nTransliterateType;" +
				"      exception" +
				"         when NO_DATA_FOUND then" +
				"            raise_application_error(-20000,'.. '||nTransliterateType||' Classified .. Const');" +
				"      end;" +
				"   else" +
				"      if nClearingHouse is not null then" +
				"         begin" +
				"            select Const into nCHConst from ClearingHouse" +
				"             where Classified = nClearingHouse or Const = nClearingHouse;" +
				"         exception" +
				"            when NO_DATA_FOUND then" +
				"               raise_application_error(-20000,'.. '||nClearingHouse||' Classified .Const');" +
				"         end;" +
				"      end if;" +
				"      if nCHConst = 1 then -- SWIFT\n" +
				"         nType := registry.GetValue('_header_included\\SWIFT\\SWIFTTransliterateType');" +
				"         if nType is null then" +
				"            raise_application_error(-20000,'... SWIFT - _header_included\\SWIFT\\SWIFTTransliterateType');" +
				"         end if;" +
				"      else -- SWIFT-RUR\n" +
				"         nType := registry.GetValue('_header_included\\SWIFT\\DefaultTransliterateType');" +
				"         if nType is null then" +
				"            raise_application_error(-20000,'.. _header_included\\SWIFT\\DefaulTransliterateType');" +
				"         end if;" +
				"      end if;" +
				"   end if;" +
				"end;" ,
				"AnonymousBlock" , 1);
		assert t.passed() :	t.getMessage();			
		
	}

	@Test
	public void testImportDeclaration() throws IOException, RecognitionException  {
		System.setProperty( Script.RUNTOOL_CURR_SCRIPT_DIR, "C:\\temp" );		
		
		Tester t = new Tester("@19mixed",	"Script", 1 );
		assert t.passed() :	t.getMessage();
		t = new Tester("@19","Script", 1 );
		assert t.passed() :	t.getMessage();
		t = new Tester("@mixed19",	"Script", 1 );
		assert t.passed() :	t.getMessage();
		t = new Tester("@mixed19.sql", "Script", 1 );
		assert t.passed() :	t.getMessage();		
		t = new Tester("@imp", "Script", 1 );
		assert t.passed() :	t.getMessage();
		t = new Tester("@imp.sql", "Script", 1 );
		assert t.passed() :	t.getMessage();
		t = new Tester("@imp;", "Script", 1 , "@imp");
		assert t.passed() :	t.getMessage();
		t = new Tester("@imp.sql;", "Script", 1, "@imp.sql" );
		assert t.passed() :	t.getMessage();
/*		t = new Tester("@..\\imp.sql",	"Script", 1 );
		assert t.passed() :	t.getMessage();
		t = new Tester("@19.sql",	"Script", 1 );
		assert t.passed() :	t.getMessage();		
 		t = new Tester("@c:\\temp\\imp.sql",	"Script", 1 ); 
 		assert t.passed() :	t.getMessage();
		t = new Tester("@.\\..\\temp\\imp.sql",	"Script", 1 );
		assert t.passed() :	t.getMessage();
*/		
	}
	

	@Test
	public void testLocalVarDecl() throws IOException, RecognitionException  {
		Tester t = new Tester(
				"create or replace procedure p as\n" +
					LOCAL_VARS_DECL_FORTEST +
				"begin null; end p;",
				"StoredObject" , 1);
		assert t.passed() :	t.getMessage();
	}

	@Test
	public void testStoredProcHeader() throws IOException, RecognitionException  {
		// no param
		Tester t = new Tester(
 				// empty param list - "()" not allowed
				"create or replace procedure someFunProc as\n" +  
				"begin	null; end;",
				"StoredObject" , 1);
		assert t.passed() :	t.getMessage();
		// one param
		t = new Tester(
				"create or replace procedure someFunProc( param number) as\n" + 
				"begin	null; end;",
				"StoredObject" , 1);
		assert t.passed() :	t.getMessage();
		// more then one param
		t = new Tester(
				"create or replace\n" +
				"procedure someFunProc( param number, par varchar2, p boolean) as\n" + 
				"begin	null; end;",
				"StoredObject" , 1);
		assert t.passed() :	t.getMessage();
	}

	@Test
	public void testStoredFunc() throws IOException, RecognitionException  {
		Tester t = new Tester(
				"create or replace\n" +
				"function someFunFunction( param number) return tNumberArry as\n" +
					CURSOR_DECL +
					LOCAL_VARS_DECL_FORTEST +
					NORMAL_FUNCTION_FORTEST + 
					NORMAL_PROC_FORTEST +
				"begin\n" +
				"	dbms(1);\n" +
				"end someFunFunction;", 
				"StoredObject" , 1);
		assert t.passed() :	t.getMessage();
		
		t = new Tester(
				"create or replace\n" +
				"function someFunFunction return number as\n" +		
				"Language Java Name " +
				"'JFile.dirContents(java.lang.String,int)  return java.lang.String';",
				"StoredObject" , 1);		
		assert t.passed() :	t.getMessage();		

		t = new Tester(
				"create or replace\n" +
				"function someFunFunction return number as\n" +		
				"external library \"lib\" Language C parameters(\n " +
				" sLeft       string,\n" +
				" nLeftLen    unsigned int);",
				"StoredObject" , 1);		
		assert t.passed() :	t.getMessage();		

	}

	@Test
	public void testTrigger() throws IOException, RecognitionException  {
		Tester t = new Tester(
				"create or replace\n" + 
				"trigger t before insert or update on table for each row\n" +
				"declare\n" +
					LOCAL_VARS_DECL_FORTEST +
					NORMAL_FUNCTION_FORTEST + 
					NORMAL_PROC_FORTEST + 
				"begin\n" +
				"	:new.ID := :old.ID;\n" +
				"end;",
				"StoredObject" , 1);
		assert t.passed() :	t.getMessage();
		t = new Tester(
				"create or replace\n" + 
				"trigger t before insert or update on table\n" +
				"begin\n" +
				"	null;\n" +
				"end t;",
				"StoredObject" , 1);
		assert t.passed() :	t.getMessage();		
	}

	@Test
	public void testSequence() throws IOException, RecognitionException  {
		Tester t = new Tester(	"create sequence s;", "StoredObject" , 1);
		assert t.passed() :	t.getMessage();
		t = new Tester(	"create sequence s start with 5 increment by 1;",
				"StoredObject" , 1);
		assert t.passed() :	t.getMessage();		
	}
	
	@Test
	public void testParamListDeclaration() throws IOException, RecognitionException  {
		Tester t = new Tester(
				"create or replace procedure someFunFunction( " +
				"	param  /* no modifiers*/ number" +
				"	,param in number" +
				"	,param out number" +
				"	,param in out number" +
				"	,param in out nocopy number" +
				"	,param  number  := 1" +				
				"	,param  number  := 'cha''r'||funcCall(0)||'set'" +				
				"	,param  number  := 5+16*25" +
				"	,param  number  := param2" +
				"	,param  number  default package1.var" +				
				"	,param  number  default package1.func(12, 55, 'status')" +				
				") as\n" +
				"begin\n null;\n end;", 
				"StoredObject" , 1);
		assert t.passed() :	t.getMessage();
	}	
	@Test
	public void testPackageSpec() throws IOException, RecognitionException  {
		Tester t = new Tester(
				"create or replace\n" +
				"package pack as \n" +
				"	locVar number := initialize(0);\n" +
				"	function localF return varchar2;\n" +
				"	procedure localProc( param1 varchar2 );\n" +
				"	pragma restict_references(WNDS,WNPS,RNDS,RNPS);\n" +
				"end;",
				"StoredObject" , 1);
		assert t.passed() :	t.getMessage();
	}
	
	@Test
	public void testPackageBody() throws IOException, RecognitionException  {
		Tester t = new Tester(
				"create or replace\n" +
				"package body pack as \n" +
				"	locVar_ number := initialize(0);\n" +
				"	function localF return varchar2 is\n" +
				"	begin\n" +
				"		localProc();\n" +
				"		callSomeOtherProc( param1, param2);\n" +
				"	exception\n" +
				"		when OTHERS then\n" +
				"			raise;\n" +
				"	end;\n" +
				"	procedure localProc( param1 varchar2 )\n" +
				"	is\n" +
				"		var1 number(10);\n" +
				"	begin\n" +
				"		var2 := someFuncValue;\n" +
				"		select * into var1 from someTable where 1=1;" +
				"	end localProc;\n" +
				"begin\n" +
				"	packageBodyCall( 1, getSomeValue( 'asd'));\n" +
				"end;",
				"StoredObject" , 1);
		assert t.passed() :	t.getMessage();
		
		t = new Tester(
				"create or replace\n" +
				"package body pack as \n" +
				"	locVar_ number := initialize(0);\n" +
				"end pack;",
				"StoredObject" , 1);
		assert t.passed() :	t.getMessage();		
	}
	
	@Test
	public void testNotSqlStatements() throws IOException, RecognitionException  {
		Tester t = new Tester(
				"create procedure test as\n" +
				"begin\n" +
				"	array.delete();\n" +
				"	array.delete(1);\n" +
				"	array.delete(n);\n" +
				"	array.delete;\n" +			
				"	dbms_lob.open(param_list);\n" +
				"	dbms_lob.close(param_list);\n" +
				"	procCall( param => value );\n" +
				"	dbms_lob.close;\n" +				
		        "   n := -n;\n" +				
				"	localProc();\n" +
				"	SetNoCheckRights;\n" +
				" 	n := m + s;\n" +
			    "   (s) := (s)||('All:')||(1 + 2)||'s';\n" +				
				"	EventLog_Start(':'||DocLabel(Context.CurrentDoc),(1=0));\n" +
				"	EventLog_Start(':'||DocLabel(Context.CurrentDoc), var=param=3);\n" +				
				"	callSomeOtherProc( param1, param2);\n" +
				"	var3 := someFuncValue;\n" +
				"	var6 := someFunctionCall(param1);\n" + 				
				"	blahBlahCall( 1, getSomeValue( 'asd'));\n" + 
				"	selector.blahBlahCall( 1, getSomeValue( 'asd'));\n" +
				"	arrayContainer.array(1) := selector.someFunctionCall(param1);\n" + 				
				"	scheme.getArray(param, varam)(itemIndex) := selector.someFunctionCall(param1);\n" +
				"	nProcessed := dbms_sql.execute(nCursor);\n" +
				"	sText := REPLACE( sText, rec.CharacterFrom, rec.CharacterTo);\n" +
				"	dbms_output.put_line(v);\n" +
				"   Message.AddReturnString( a.b('d', c.d()||f.e() ) );\n" +
				"end;",
				"StoredObject" , 1);
		assert t.passed() :	t.getMessage();
	}

	@Test
	public void testSqlStatements() throws IOException, RecognitionException  {
		Tester t = new Tester(
				"begin\n" +
				"	select * into rec from dual;\n" +
				"	update dual set dummy = 'Y' where rownum < 2;\n" +
				"	insert into dual(dummy) values('Y');\n" +
				"	delete dual;\n" +
				"	delete from dual;\n" +
				"	open cur;\n" +
				"	open cur();\n" +
				"	open cur( param, 0);\n" +
				"	fetch cur into var;\n" +
				"	close cur;" +
				"	return cur%rowcount;" +				
				"	return cur%isopen;" +				
				"end;",
				"AnonymousBlock" , 1);
		assert t.passed() :	t.getMessage();
	}


	@Test
	public void testDynamicStatements() throws IOException, RecognitionException  {
		Tester t = new Tester(
				"begin\n" +
				"	execute immediate 'ss';\n" +
				"	execute immediate 'ss'||func();\n" +
				"	execute immediate 'ss'||f()||'a';\n" +				
				"	execute immediate f()||var;\n" +				
				"	execute immediate f()||':sd' using in out var;\n" +
				"	execute immediate f()||':sd' using in out var,4,5;\n" +				
				"	open c for 'as';\n" +
				"	open c for var;\n" +				
				"	open c for getStatement();\n" +				
				"end;",
				"AnonymousBlock" , 1);
		assert t.passed() :	t.getMessage();
	}
	
	@Test
	public void testLoopStatements() throws IOException, RecognitionException  {
		Tester t = new Tester(
				"begin\n" +
				"	for rec in (select 1,2 from dual " +
				"				where funcCall( param1, n) ) loop\n" +
				"		null;\n" +
				"	end LOOP;\n" +
				"	LOOP\n" +
				"		exit when false;\n" +
				"		exit when sql%notfound;\n" +
				"		exit when cursor_name%notfound;\n" +				
				"	enD lOOp;\n" +	
				"	for r in 0..getLastIndex( param, param) loop\n" +
				"		for nl in (select * from dual where 1=1 and 5 > 3 ) loop\n" +
				"			null;\n" +
				"		end loop;\n" +
				"	end loop;\n" +
				"	while true loop\n" +
				"		exit;\n" +
				"	end loop;\n" +
				"	for rec in cursorCur(param1, 0 , false) loop\n" +
				"		exit when condition is true or false is true;\n" +
				"	end loop;" +
				"end;",
				"AnonymousBlock" , 1);
		assert t.passed() :	t.getMessage();
	}

	@Test
	public void testTableStatements() throws IOException, RecognitionException  {
		Tester t = new Tester(
				"create table tab1 (\n" +
				"	fieldNum		number PRIMARY KEY,\n" +
				"	fieldStr		varchar2(254)\n" +
				");" ,
				"StoredObject" , 1);
		assert t.passed() :	t.getMessage();

		t = new Tester(
				"create temporary table tab1 (\n" +
				"	fieldNum		number PRIMARY KEY,\n" +
				"	fieldStr		varchar2(254)\n" +
				");" ,
				"StoredObject" , 1);
		assert t.passed() :	t.getMessage();

		t = new Tester(
				"create global temporary table tab1 (\n" +
				"	fieldNum		number PRIMARY KEY,\n" +
				"	fieldStr		varchar2(254)\n" +
				");" ,
				"StoredObject" , 1);
		assert t.passed() :	t.getMessage();
		
	}

	@Test
	public void testIndexStatements() throws IOException, RecognitionException  {
		Tester t = new Tester(
				"create index ix_U \n" +
				"	on table(field);",
				"StoredObject" , 1);
		assert t.passed() :	t.getMessage();

	}	
/*
	@Test
	public void testIt() throws IOException, RecognitionException  {
		Tester t = new Tester("exec SomeProc;","AnonymousBlock" , 1,
				"begin SomeProc; end;");
		assert t.passed() :	t.getMessage();
	} 
*/


	private class Tester extends Object {
		private boolean passed_;
		private String 	body_;
		private String message_;
		private CodeBlock result_;
		List<CodeBlock> codeBlocksList_ = null;
		
		public Tester( String text, String className, int resCount, String result) {
			body_ = text;
			PLSQLParser parser = null;
			try {
				parser = createParser( body_ );
			} catch (IOException e) {
				 passed_ = false;
				 message_ = e.getMessage();
				 return;
			}
			
			try {
				codeBlocksList_ = parser.compilationUnits();
			} catch (RecognitionException e) {
				 passed_ = false;
				 message_ = e.getMessage();
				 return;
			}
		
			if ( codeBlocksList_.get(0) instanceof CodeBlock ) {
				result_ = (CodeBlock) codeBlocksList_.get(0);
			} else {
				assert false : "cast to CodeBlock fail!";
			}
			
			if (result != null ) {
				// this block must be transformed
				body_ = result;
			}
			
			passed_ = false;
			if ( result_ instanceof StoredObject ) {
				passed_ = className.equals( result_.getShortClassName() );
			} else {
				passed_ = true;
			}
		    passed_ = passed_ && 
		    	result_.getText().equals(body_) 
		    	&& resCount == codeBlocksList_.size();		    	
			
			message_ = "Expected "+ resCount + " <" + className + ">\n<" + 
							body_ + ">\n" +
						"got " + codeBlocksList_.size() + " <" + 
						codeBlocksList_.get(0).getShortClassName() + ">\n" +
						"<" + result_.getText() + ">";
		}
		
		public Tester( String text, String className, int resCount) {
			this( text, className, resCount, null);
		}
	
		public boolean passed() {
			return passed_;
		}
		
		public String getMessage() {
			return message_;
		}

		public String getFirstBlockClassName() {
			return codeBlocksList_.get(0).getClass().getName();
		}
				
		private PLSQLParser createParser(String testString) throws IOException {
			PLSQLLexer lexer = createLexer(testString);
			CommonTokenStream tokens = new CommonTokenStream(lexer);
			PLSQLParser parser = new PLSQLParser(tokens);
			return parser;
		}

		private PLSQLLexer createLexer(String testString) throws IOException {
			CharStream stream = new CaseInsensitiveStringStream(testString, "windows-1251" );
			PLSQLLexer lexer = new PLSQLLexer(stream);
			return lexer;
		}
		
	}
	
}
